# -*- coding:utf-8 -*-
from datetime import datetime, timedelta

import torndb

import setting
from util.mail_sender import MailSender

class TodayStatsMailPush(object):

    def __init__(self):

        self.db = torndb.Connection(
            host=setting.mysql_host,
            database=setting.mysql_database,
            user=setting.mysql_user,
            password=setting.mysql_password,
            time_zone="+8:00",
            charset='utf8mb4',
            max_idle_time=900,
            connect_timeout=900
        )

        self.mail = MailSender()

    def _get_today_apply_monthcard(self, start_date, end_date):
        return self.db.query("select id, `name`, mobile, cardno, apply_start_time "
                      "  from t_monthcard_apply_record "
                      " where create_date >= %(start_date)s "
                      "   and create_date <= %(end_date)s order by create_date desc  ", start_date=start_date, end_date=end_date)

    def _get_order(self, start_date, end_date):
        return self.db.query("select o.id, o.total_fee, o.create_date, o.paytime, o.coupon_price, op.product_id, op.cnt, op.source, p.title, w.nickname "
                             "  from t_order o, r_order_product op, t_product p, t_wechat w "
                             " where o.status = 1 "
                             "   and o.create_date >= %(start_date)s "
                             "   and o.create_date <= %(end_date)s "
                             "   and o.id = op.order_id "
                             "   and p.id = op.product_id "
                             "   and w.user_id = o.user_id "
                             " order by o.create_date desc ", start_date=start_date, end_date=end_date)


    def _get_extraction_distribution(self, start_date, end_date):
        return self.db.query("select pdr.id, w.nickname, pdr.mobile, pdr.name, pdr.extraction_time, l.name as loungehall_name, "
                             "       pdr.create_date "
                             "  from t_distribution_record pdr, t_wechat w, t_loungehall l "
                             " where pdr.type=1 "
                             "   and pdr.create_date >= %(start_date)s "
                             "   and pdr.create_date <= %(end_date)s "
                             "   and w.user_id = pdr.user_id "
                             "   and l.id = pdr.loungehall_id "
                             " order by pdr.create_date desc ", start_date=start_date, end_date=end_date)

    def _get_distribution(self, start_date, end_date):
        return self.db.query("select pdr.id, w.nickname, pdr.mobile, pdr.name, pdr.province, pdr.city, pdr.address, "
                             "       pdr.create_date "
                             "  from t_distribution_record pdr, t_wechat w "
                             " where pdr.type=2 "
                             "   and pdr.create_date >= %(start_date)s "
                             "   and pdr.create_date <= %(end_date)s "
                             "   and w.user_id = pdr.user_id "
                             " order by pdr.create_date desc ", start_date=start_date, end_date=end_date)

    def _get_product_titles(self, distribution_id):
        products = self.db.query("select concat(p.title,dp.cnt,p.unit) title "
                                 "  from r_distribution_product dp, t_product p "
                                 " where dp.distribution_id=%(distribution_id)s "
                                 "   and p.id=dp.product_id ", distribution_id=distribution_id)

        titles = map(lambda x: x["title"], products)
        return u",".join(titles)

    def _get_oncecard_consume(self, start_date, end_date):
        return self.db.query("select ucor.id, w.nickname, l.name, ucor.flight, ucor.name, ucor.mobile, ucor.create_date "
                             "  from t_user_consume_oncecard_record ucor, t_loungehall l, t_wechat w "
                             " where l.id = ucor.loungehall_id "
                             "   and w.user_id=ucor.user_id "
                             " where ucor.create_date >= %(start_date)s "
                             "   and ucor.create_date <= %(end_date)s "
                             " order by ucor.create_date desc ", start_date=start_date, end_date=end_date)

    def _get_receive_gift(self, start_date, end_date):
        return self.db.query("select gpr.id, g.gift_nickname, g.gift_time, gpr.nickname as receive_nickname, gpr.receive_time, p.title "
                             "  from r_gift_product_receive gpr, (select g.create_date as gift_time, w.nickname as gift_nickname from t_gift g, t_wechat w where g.user_id = w.user_id) g, t_wechat w, t_product p "
                             " where gpr.status=1 "
                             "   and gpr.receive_time >= %(start_date)s "
                             "   and gpr.receive_time <= %(end_date)s "
                             "   and w.user_id = gpr.receiver_id "
                             "   and g.id = gpr.gift_id "
                             "   and p.id = gpr.product_id "
                             " order by gpr.receive_time desc ", start_date=start_date, end_date=end_date)

    def _get_product_inventory(self):
        return self.db.query("select p.title, p.total, o.cnt, (p.total-o.cnt) balanace "
                             "  from t_product p, (select op.product_id, sum(cnt) cnt from r_order_product op, (select id from t_order where status = 1) t where t.id = op.order_id and op.source in (0, 1) group by product_id) o "
                             " where o.product_id = p.id order by p.id ")

    def _prepare_mail_txt(self, today, start_date, end_date):

        # 开通会员数据信息
        html = u'<p style="text-align:center;font-size:14px;">{} 开通月体验卡数据信息</p>'.format(today)
        html += u'<p><table id="tab" style="width:600px;" cellpadding="2" cellspacing="0" align="center" border="1" bordercolor="#000000">'
        html += u'<tbody><tr><td>ID</td>'
        html += u'<td style="text-align:center;font-size:14px;">姓名</td>'
        html += u'<td style="text-align:center;font-size:14px;">手机号</td>'
        html += u'<td style="text-align:center;font-size:14px;">身份证号</td>'
        html += u'<td style="text-align:center;font-size:14px;">申请开卡日期</td></tr>'

        data = self._get_today_apply_monthcard(start_date, end_date)
        if data:
            for m in data:
                html += u'<tr>'
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(m.id)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(m.name)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(m.mobile)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(m.cardno)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(str(m.apply_start_time)[:10])
                html += u'</tr>'

        html += u'</tbody></table></p>'

        # 今日订单信息
        html += u'<p style="text-align:center;font-size:14px;">{} 商品订单数据</p>'.format(today)
        html += u'<p><table id="tab" style="width:600px;" cellpadding="2" cellspacing="0" align="center" border="1" bordercolor="#000000">'
        html += u'<tbody><tr><td>ID</td>'
        html += u'<td style="text-align:center;font-size:14px;">昵称</td>'
        html += u'<td style="text-align:center;font-size:14px;">商品名称</td>'
        html += u'<td style="text-align:center;font-size:14px;">商品数量</td>'
        html += u'<td style="text-align:center;font-size:14px;">下单时间</td>'
        html += u'<td style="text-align:center;font-size:14px;">支付时间</td>'
        html += u'<td style="text-align:center;font-size:14px;">支付金额</td>'
        html += u'<td style="text-align:center;font-size:14px;">使用优惠券金额</td>'
        html += u'<td style="text-align:center;font-size:14px;">产生方式</td></tr>'

        data = self._get_order(start_date, end_date)
        if data:
            for o in data:

                if o.source == 1:
                    source = u"支付购买"
                elif o.source == 0:
                    source = u"手动给高管添加"
                else:
                    source = u"赠送领取"

                html += u'<tr>'
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.id)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.nickname)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.title)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.cnt)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(str(o.create_date)[:10])
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(str(o.paytime)[:10] if o.paytime else u'')
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.total_fee/100 if o.total_fee > 0 else o.total_fee)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.coupon_price/100 if o.coupon_price > 0 else u'')
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(source)
                html += u'</tr>'

        html += u'</tbody></table></p>'

        # 今日自提信息
        html += u'<p style="text-align:center;font-size:14px;">{} 商品自提数据</p>'.format(today)
        html += u'<p><table id="tab" style="width:600px;" cellpadding="2" cellspacing="0" align="center" border="1" bordercolor="#000000">'
        html += u'<tbody><tr><td>ID</td>'
        html += u'<td style="text-align:center;font-size:14px;">昵称</td>'
        html += u'<td style="text-align:center;font-size:14px;">商品名称</td>'
        html += u'<td style="text-align:center;font-size:14px;">姓名</td>'
        html += u'<td style="text-align:center;font-size:14px;">手机号</td>'
        html += u'<td style="text-align:center;font-size:14px;">自提点</td>'
        html += u'<td style="text-align:center;font-size:14px;">提取时间</td>'
        html += u'<td style="text-align:center;font-size:14px;">创建时间</td></tr>'

        data = self._get_extraction_distribution(start_date, end_date)
        if data:
            for o in data:

                title = self._get_product_titles(o.id)

                html += u'<tr>'
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.id)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.nickname)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(title)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.name)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.mobile)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.loungehall_name)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(str(o.extraction_time)[:10])
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(str(o.create_date)[:10])
                html += u'</tr>'

        html += u'</tbody></table></p>'

        # 今日配送信息
        html += u'<p style="text-align:center;font-size:14px;">{} 商品配送数据</p>'.format(today)
        html += u'<p><table id="tab" style="width:600px;" cellpadding="2" cellspacing="0" align="center" border="1" bordercolor="#000000">'
        html += u'<tbody><tr><td>ID</td>'
        html += u'<td style="text-align:center;font-size:14px;">昵称</td>'
        html += u'<td style="text-align:center;font-size:14px;">商品名称</td>'
        html += u'<td style="text-align:center;font-size:14px;">姓名</td>'
        html += u'<td style="text-align:center;font-size:14px;">手机号</td>'
        html += u'<td style="text-align:center;font-size:14px;">省</td>'
        html += u'<td style="text-align:center;font-size:14px;">市</td>'
        html += u'<td style="text-align:center;font-size:14px;">详细地址</td>'
        html += u'<td style="text-align:center;font-size:14px;">创建时间</td></tr>'

        data = self._get_distribution(start_date, end_date)
        if data:
            for o in data:

                title = self._get_product_titles(o.id)

                html += u'<tr>'
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.id)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.nickname)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(title)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.name)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.mobile)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.province)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.city)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.address)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(str(o.create_date)[:10])
                html += u'</tr>'

        html += u'</tbody></table></p>'

        # 今日赠送领取信息
        html += u'<p style="text-align:center;font-size:14px;">{} 商品库存信息</p>'.format(today)
        html += u'<p><table id="tab" style="width:600px;" cellpadding="2" cellspacing="0" align="center" border="1" bordercolor="#000000">'
        html += u'<tbody><tr><td>商品名称</td>'
        html += u'<td style="text-align:center;font-size:14px;">商品总数</td>'
        html += u'<td style="text-align:center;font-size:14px;">售出数量</td>'
        html += u'<td style="text-align:center;font-size:14px;">剩余数量</td>'

        data = self._get_product_inventory()
        if data:
            for o in data:
                html += u'<tr>'
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.title)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.total)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.cnt)
                html += u'<td style="text-align:center;font-size:14px;">{}</td>'.format(o.balanace)

                html += u'</tr>'

        # 商品库存数量

        html += u'</tbody></table></p>'

        return html

    def runner_data(self, today, start_date, end_date):

        html = self._prepare_mail_txt(today, start_date, end_date)
        subject = u'空铁管家微商城数据日推送 {}'.format(today)

        if html:
            self.mail._send_mail(self.mail.receivers, subject, html)

    def runner(self):

        now = datetime.now()
        # 每日统计数据日志
        today_date = now + timedelta(days=-1)
        today = today_date.strftime("%Y-%m-%d")
        start_date = today_date.strftime("%Y-%m-%d 00:00:00")
        end_date = today_date.strftime("%Y-%m-%d 23:59:59")

        self.runner_data(today, start_date, end_date)

if __name__ == '__main__':
    print "run start : {}".format(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    push = TodayStatsMailPush()
    push.runner()
    push.db.close()
    print "run end : {}".format(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
